Homework 3
Link to my GITHUB Repo: https://github.com/cmsc-vcu/cmsc408-sp2025-hw3-serrotrehpotsirhc
Introduction
In this project, we explore Entity-Relationship (ER) modeling by analyzing three real-world database scenarios: a University Course Enrollment System, a Hospital Patient Management System, and a Movie Theater Booking System.
The objective is to define entities, attributes, and relationships for each scenario, visually represent them using Chen’s Notation and Crow’s Foot Notation, and translate them into relational schemas.
These steps reflect the progression from conceptual design to logical database structuring, which is a critical process in database development.
I will also be describing design choices for each, following the Sports Tournament Management System model provided.
Beyond diagramming, this project also emphasizes design choices related to normalization, cardinality, and relationship modeling.
Homework 3 will provide us more experience using Graphiz and Mermaid, as well as allowing us to begin to understand how to realize conceptual models in ER modelling and beyond! Homework 3 continues to get us familiar with these databases tools and lets us use our skills on our own examples this time.
Technologies Used
- Graphviz for Chen’s Notation
- Mermaid for Crow’s Foot Notation
Models
Below are three entity-relation models for systems in the world around me.
Model 1 - University Course Enrollment System
Consider a university that wants to manage students, courses, and professors. Each student has a student ID, name, and major. Courses have a course ID, title, and credit hours. Professors have a professor ID, name, and department. A student can enroll in multiple courses, and each course can have multiple students. A professor can teach multiple courses, but each course is taught by a single professor.
Entities:
- Students
- Courses
- Professors
Schema:
- Each student has: a student ID, name, and major.
- Each course has: a course ID, title, and credit hours.
- Each professor has: a professor ID, name, and department.
Cardinality:
A student can enroll in multiple courses, and each course can have multiple students.
A professor can teach multiple courses, but each course is taught by a single professor.
Design choices
Why These Entities?
Student → Central to any university enrollment system.
Course → The core offering students enroll in.
Professor → Faculty members who teach courses.
Why These Cardinalities:
- Students to Courses (Many-to-Many):
A student can enroll in multiple courses, and a course can have multiple students.
- Professors to Courses (One-to-Many):
A professor can teach multiple courses, but each course is taught by a single professor.
Why These Participations:
- Students → Courses (Partial Participation):
Not all students may enroll in a course.
- Courses → Students (Total Participation):
A course must have at least one student enrolled (otherwise, it shouldn’t exist).
- Professors → Courses (Total Participation):
Every course must be assigned a professor.
- Courses → Professors (Partial Participation):
A professor may teach multiple courses, but they are not required to teach any.
Chen Diagram
Crows Foot diagram
erDiagram
STUDENT {
int studentID
string name
string major
}
COURSE {
int courseID
string title
int credit_hours
}
PROFESSOR {
int professorID
string name
string department
}
STUDENT ||--o{ COURSE : enrolls
PROFESSOR ||--o{ COURSE : teaches
Relational model
Relations and Attributes:
- Student (studentID, name, major)
- Course (courseID, title, credit_hours)
- Professor (professorID, name, department)
Primary and Foreign Keys:
- Student: studentID (PK)
- Course: courseID (PK)
- Professor: professorID (PK)
Model 2 - Hospital Patient Management System
Consider a hospital that wants to manage patients, doctors, and appointments. Each patient has a patient ID, name, and date of birth. Doctors have a doctor ID, name, and specialty. Appointments have an appointment ID, date, and time. A patient can have multiple appointments, and each appointment involves one patient. A doctor can have multiple appointments, but each appointment is handled by a single doctor.
Entities:
Patients
Doctors
Appointments
Schema:
Each patient has: a patient ID, name, and date of birth.
Each doctor has: a doctor ID, name, and specialty.
Each appointment has: an appointment ID, date, and time.
Cardinality:
A patient can have multiple appointments, and each appointment involves one patient.
A doctor can have multiple appointments, but each appointment is handled by a single doctor.
Design choices
Why These Entities?
Patient → Represents individuals receiving medical care.
Doctor → Represents medical professionals providing treatment.
Appointment → The event linking patients and doctors.
Why These Cardinalities:
- Patients to Has to Appointments (One-to-Many):
A patient can have multiple appointments, but each appointment is linked to one patient.
- Doctors to Handles to Appointments (One-to-Many):
A doctor can have multiple appointments, but each appointment is handled by a single doctor.
Why These Participations:
- Patients → Has (Partial Participation):
A patient may not have any appointments.
- Appointments → Has (Total Participation):
Every appointment must be linked to a patient.
- Doctors → Handles (Partial Participation):
A doctor may not have any scheduled appointments.
- Appointments → Handles (Total Participation):
Every appointment must have a doctor assigned.
Chen Diagram
Crows Foot diagram
erDiagram
PATIENT {
int patientID
string name
date dob
}
APPOINTMENT {
int appointmentID
date date
time time
}
DOCTOR {
int doctorID
string name
string specialty
}
PATIENT ||--o{ APPOINTMENT : has
DOCTOR ||--o{ APPOINTMENT : handles
Relational model
Relations and Attributes:
- Patient (patientID, name, dob)
- Doctor (doctorID, name, specialty)
- Appointment (appointmentID, date, time, patientID, doctorID)
Primary and Foreign Keys:
Patient: patientID (PK)
Doctor: doctorID (PK)
Appointment: appointmentID (PK), patientID (FK → Patient), doctorID (FK → Doctor)
Model 3 - Movie Theater Booking System
Consider a movie theater that wants to manage customers, movies, and screenings. Each customer has a customer ID, name, and membership status. Movies have a movie ID, title, and duration. Screenings have a screening ID, date, and time. A customer can attend multiple screenings, and each screening can have multiple customers. A movie can have multiple screenings, but each screening features a single movie.
Entities:
Customers
Movies
Screening
Schema:
Each customer has: a customer ID, name, and membership status.
Each movie has a movie ID, title, and duration
Each screening has a screening ID, date, and time.
Cardinality:
A customer can attend multiple screenings, and each screening can have multiple customers.
A movie can have multiple screenings, but each screening features a single movie.
Design choices
Why These Entities?
Customer → Represents individuals purchasing tickets.
Movie → The content being screened.
Screening → Represents a specific showing of a movie at a given time.
Why These Cardinalities:
- Customers to Books to Screenings (Many-to-Many):
A customer can book multiple screenings, and each screening can have multiple customers.
- Movies to Scheduled to Screenings (One-to-Many):
A movie can have multiple screenings, but each screening is for a single movie.
Why These Participations:
- Customers → Books (Partial Participation):
A customer may not book any screenings.
- Screenings → Books (Total Participation):
A screening must have at least one customer booked.
- Movies → Scheduled (Total Participation):
A movie must be scheduled for at least one screening.
- Screenings → Scheduled (Partial Participation):
Each screening must be linked to a single movie.
Chen Diagram
Crows Foot diagram
erDiagram
CUSTOMER {
int customerID
string name
string membership_status
}
MOVIE {
int movieID
string title
int duration
}
SCREENING {
int screeningID
date date
time time
}
CUSTOMER ||--o{ SCREENING : books
MOVIE ||--o{ SCREENING : scheduled
Relational model
Relations And Attributes:
Customer (customerID, name, membership_status)
Movie (movieID, title, duration)
Screening (screeningID, date, time, movieID)
Booking (customerID, screeningID) → (Bridge table for many-to-many relationship)
Primary And Foreign Keys:
Customer: customerID (PK)
Movie: movieID (PK)
Screening: screeningID (PK), movieID (FK → Movie)
Booking: customerID (FK → Customer), screeningID (FK → Screening) (Composite PK)
README
Welcome to Homework 3
Project Overview
This project explores Entity-Relationship (ER) modeling by defining and visualizing three real-world database scenarios. The project includes:
- Scenario Design: Defining three database scenarios with entities, attributes, and relationships.
- Graphical Representation: Using Chen’s Notation (Graphviz) and Crow’s Foot Notation (Mermaid) to model each scenario.
- Relational Model Construction: Converting each ER model into relational schemas.
- Design Choices Explanation: Documenting the rationale behind modeling decisions.
Scenarios Modeled
- University Course Enrollment System
- Models students, courses, and professors.
- Defines enrollment and teaching relationships.
- Models students, courses, and professors.
- Hospital Patient Management System
- Represents patients, doctors, and appointments.
- Captures how appointments are scheduled and handled.
- Represents patients, doctors, and appointments.
- Movie Theater Booking System
- Covers customers, movies, and screenings.
- Models how customers book screenings.
- Covers customers, movies, and screenings.
Graphical Representations
Each scenario is modeled in two notations:
- Chen’s Notation → Illustrates entities, attributes, and relationships with diamonds.
- Crow’s Foot Notation → Shows relationships with cardinality symbols for better database structuring.
Relational Models
Each ER model was transformed into relational schemas with:
- Tables & Attributes
- Primary and Foreign Keys
- Bridge Tables (where necessary for many-to-many relationships)
Design Choices
Normalization & Cardinality
- Many-to-Many Relationships (e.g., students enrolling in courses, customers booking screenings) were handled using bridge tables to avoid redundancy.
- One-to-Many Relationships (e.g., doctors handling appointments, professors teaching courses) were directly mapped using foreign keys to maintain referential integrity.
Graphviz vs. Mermaid
- Graphviz (Chen’s Notation) was used for detailed conceptual modeling, making attribute representation clearer.
- Mermaid (Crow’s Foot Notation) was chosen for logical database design, which aligns more closely with relational modeling.
Why These Scenarios?
- They represent common real-world database applications used in education, healthcare, and entertainment.
- They demonstrate different levels of complexity, from simple one-to-many relationships (hospital system) to complex many-to-many relationships (course enrollment).
Quarto will render report.qmd in the reports folder into the report.html file.
Further examples found in the example folder!
Thank you! - Christopher Torres
Reflection
- What was the most surprising (good or bad) thing that you learned by doing this assignment?
-
The most surprising thing I learned from Homework 3 was the amount of work and how important it is to have a great database design from the beginning. When we are first creating database designs from our own examples, having effienct conceptions of the problem and efficient relationships in the problem is essential for creating a database which is efficient itself. Making sure this step is clear in the process, which is efficientlay crafting databases from the beginning is crucial in database desing. I am glad I got practice learning ER modeling from my own examples.
- How much time did you spend on this assignment?
-
I spend roughly 1 hour and 45 minutes on this assignment since its structure was already pretty similar to Homework 2.
- What CS classes have you had that the quarto math expressions and diagrams would have made it easier?
-
The only classes I can think of which it would have made life easier might have been my website design and development class as well as Algorithms & Data Structures, which I took back in 2022. It also might have been useful in my Discrete Math course.